import mysql.connector
import math
import pandas as pd
import numpy as np


#数据库连接封装

class DatabaseConnector:
    def __init__(self, host, port, user, password, database, charset='utf8'):
        self.config = {
            'host': host,
            'port': port,
            'user': user,
            'password': password,
            'database': database,
            'charset': charset
        }
        self.connection = None

    def connect(self):
        self.connection = mysql.connector.connect(**self.config)

    def disconnect(self):
        if self.connection:
            self.connection.close()

    def execute_query(self, query, args=None):
        cursor = self.connection.cursor()
        cursor.execute(query, args)
        result = cursor.fetchall()
        cursor.close()
        return result
    
    def update_record(self, query):
        cursor = self.connection.cursor()
        cursor.execute(query)
        self.connection.commit()
        cursor.close()

    def convert_type(self, value):
        """
        将numpy类型转换为Python内置类型
        """
        if isinstance(value, (np.integer, np.int64)):
            return int(value)
        if isinstance(value, (np.floating, np.float64)):
            return float(value)
        if isinstance(value, (np.str_, np.unicode_)):
            return str(value)
        return value





# 计算热度值的函数
# 数据库实时运行之前不要使用衰减
def calculate_post_hotness(views, likes, total_comments, total_comment_likes, days_since_posted, gravity_factor=0.5):
    # 加权求和计算热度基准值 Ha
    base_hotness = 0.01 * views + 1 * likes + 1 * total_comments + 0.1 * total_comment_likes

    # 对数衰减计算热度值 H(t)
    # hotness = base_hotness * math.exp(-gravity_factor * days_since_posted)
    return base_hotness





# 计算热度变化
# 引入封装的连接器
def calculate_and_update_hotval_rate(db_connector, record_id):
    # 查询该记录所属的pid
    pid_query = f"SELECT pid FROM pop_record WHERE id = {record_id}"
    pid_result = db_connector.execute_query(pid_query)
    if not pid_result:
        print("记录ID未找到")
        return
    pid = pid_result[0][0]

    # 查询该pid的所有记录，按时间排序
    query = f"SELECT pid, id, hotVal_rate, viewNum, likeNum, comNum, c_likeNum, recordTime FROM pop_record WHERE pid = {pid} ORDER BY recordTime"
    rows = db_connector.execute_query(query)

    # 将记录存储到DataFrame
    columns = ['pid', 'id', 'hotVal_rate', 'viewNum', 'likeNum', 'comNum', 'c_likeNum', 'recordTime']
    df = pd.DataFrame(rows, columns=columns)

    # 将recordTime转换为datetime类型
    df['recordTime'] = pd.to_datetime(df['recordTime'])

    # 遍历记录行
    for i in range(len(df)):
        pid, id, hotVal_rate, viewNum, likeNum, comNum, c_likeNum, time = df.iloc[i]


        # 通过是否有浏览量判断帖子记录是否有效（一般而言不会有0浏览量的帖子存在）
        if viewNum:
            # 检查是否存在上一次记录
            if i > 0 and df.iloc[i - 1]['pid'] == pid:
                last_hotVal_rate, last_viewNum, last_likeNum, last_comNum, last_c_likeNum, last_time = df.iloc[i - 1][['hotVal_rate', 'viewNum', 'likeNum', 'comNum', 'c_likeNum', 'recordTime']]

                # 计算时间差
                time_diff = time - last_time
                hours_diff = time_diff.total_seconds() / 3600

                # 检查时间差是否大于等于1小时
                if hours_diff >= 0.9:
                    # 计算hotVal_rate的差值
                    hotVal_rate_diff = 0.1 * viewNum + likeNum + comNum + c_likeNum - (0.1 * last_viewNum + last_likeNum + last_comNum + last_c_likeNum)

                    # 计算插值
                    hotVal_rate = hotVal_rate_diff / hours_diff

                    # 更新记录的hotVal_rate
                    update_query = f"UPDATE pop_record SET hotVal_rate = {hotVal_rate} WHERE id = {id}"
                    db_connector.update_record(update_query)
                else:
                    # 寻找满足条件的记录
                    found = False
                    for j in range(i - 2, -1, -1):
                        prev_record = df.iloc[j]
                        if prev_record['pid'] != pid:
                            # 到达该帖子最早记录边界
                            break
                        prev_time_diff = last_time - prev_record['recordTime']
                        prev_hours_diff = prev_time_diff.total_seconds() / 3600

                        if prev_hours_diff >= 1:
                            # 计算hotVal_rate的差值
                            hotVal_rate_diff = 0.1 * last_viewNum + last_likeNum + last_comNum + last_c_likeNum - (0.1 * prev_record['viewNum'] + prev_record['likeNum'] + prev_record['comNum'] + prev_record['c_likeNum'])

                            # 计算插值
                            hotVal_rate = hotVal_rate_diff / prev_hours_diff

                            # 更新记录的hotVal_rate
                            update_query = f"UPDATE pop_record SET hotVal_rate = {hotVal_rate} WHERE id = {id}"
                            db_connector.update_record(update_query)
                            found = True
                            break

                    if not found:
                        if np.isnan(last_hotVal_rate) and np.isinf(last_hotVal_rate):
                            last_hotVal_rate = 0
                        update_query = f"UPDATE pop_record SET hotVal_rate = {last_hotVal_rate} WHERE id = {id}"
                        db_connector.update_record(update_query)
            else:
                update_query = f"UPDATE pop_record SET hotVal_rate = 0 WHERE id = {id}"
                db_connector.update_record(update_query)




def main():
    db_connector = DatabaseConnector(
        host="sse-21311572-train.mysql.rds.aliyuncs.com",
        port="3307",
        user="SSE_user1",
        password="SSE_user1test",
        database="sse_training"
    )
    db_connector.connect()
    record_id = 4855  # 输入的记录ID
    calculate_and_update_hotval_rate(db_connector, record_id)
    db_connector.disconnect()
    


if __name__ == "__main__":
    main()
    
    
    
    
    
    
    
    
    
    





#函数：统计某帖子最新的评论点赞总数
def update_like_num(post_id):
    # 创建连接对象
    cnx = mysql.connector.connect(
        host="sse-21311572-train.mysql.rds.aliyuncs.com",
        port="3307",
        user="SSE_user1",
        password="SSE_user1test",
        database="sse_training",
        charset='utf8'
    )

    # 创建游标对象
    cursor = cnx.cursor()

    try:
        # 查询评论表中指定帖子的所有评论的点赞数总和
        query = "SELECT SUM(likeNum) FROM comments WHERE pid = %s"
        cursor.execute(query, (post_id,))
        result = cursor.fetchone()
        total_likes = result[0] if result[0] is not None else 0

        # 更新pop_record表中对应帖子的点赞数
        update_query = "UPDATE pop_record SET c_likeNum = %s WHERE pid = %s"
        cursor.execute(update_query, (total_likes, post_id))

        # 提交事务
        cnx.commit()

        print("点赞数更新成功")
    except Exception as e:
        print("点赞数更新失败:", str(e))
    finally:
        # 关闭游标和连接
        cursor.close()
        cnx.close()

# # 使用示例
# post_id = 123  # 帖子ID
# update_like_num(post_id)